var express = require('express');
var Msdb = require("../../../db/Mysqldb.js")
var router = express.Router()   //可使用 express.Router 类创建模块化、可挂载的路由句柄

var db = new Msdb()
var getTime=()=>{
    return new Date(new Date().getTime()+8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');//获取系统时间并转为数据库接受的格式
}

var express = require('express');
var Msdb = require("../../../db/Mysqldb.js")
var router = express.Router();

var db = new Msdb();

router.post("/getProducts", async function(req, res) {
    const result = {
        code: 200,
        msg: "查询成功",
        data: [],
        total: 0
    };
    
    const { sales_order_id, sn, pageSize, offset } = req.body;
    
    try {
        // 基础SQL语句，查询产品表
        let query = "SELECT * FROM product";
        let countQuery = "SELECT COUNT(*) AS total FROM product";
        const queryParams = [];
        const whereConditions = [];
        
        // 销售单号查询 - 完全匹配
        if (sales_order_id && sales_order_id.trim() !== "") {
            whereConditions.push("sales_order_id = ?");
            queryParams.push(sales_order_id.trim());
        }
        
        // SN码查询 - 模糊匹配，支持边输入边查询
        if (sn && sn.trim() !== "") {
            whereConditions.push("sn IS NOT NULL AND sn LIKE ?");
            queryParams.push(`%${sn.trim()}%`);
        }
        
        // 添加WHERE条件
        if (whereConditions.length > 0) {
            const whereClause = " WHERE " + whereConditions.join(" AND ");
            query += whereClause;
            countQuery += whereClause;
        }
        
        // 保存查询参数用于总数查询
        const countParams = [...queryParams];
        
        // 添加分页条件
        query += " LIMIT ? OFFSET ?";
        queryParams.push(pageSize, offset);
        
        // 执行查询
        const products = await db.query(query, queryParams);
        const [countResult] = await db.query(countQuery, countParams);
        
        result.data = products;
        result.total = countResult.total;
        
    } catch (error) {
        result.code = 500;
        result.msg = "查询失败: " + error.message;
    }
    
    res.json(result);
});

module.exports = router